package benny_driving_ser.dao.impl;

import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import benny_driving_ser.ObjectBean.DriXYBean;
import benny_driving_ser.dao.DriRandomBonusDao;
import benny_driving_ser.dao.FormDao;
import benny_driving_ser.entity.Form;
import benny_driving_ser.util.DBUtil;
import benny_driving_ser.util.FormatUtil;
import benny_driving_ser.util.LatitudeUtils;
import benny_driving_ser.util.MilUtil;
import benny_driving_ser.util.SendSMSUtil;

public class FormDaoImpl implements FormDao {

	private FormDaoImpl() {
	}

	private static class FormDaoImplHolder {
		private static FormDaoImpl instance = new FormDaoImpl();
	}

	public static FormDaoImpl getInstance() {
		return FormDaoImplHolder.instance;
	}

	public String createForm(int userid, String startPos, String desPos,
			String exCost, String exmil, long sertime, String username,
			String usertel) {
		if (sertime - System.currentTimeMillis() < 30 * 60 * 1000
				&& sertime - System.currentTimeMillis() > 24 * 60 * 60 * 1000)
			return "3";
		String sql_insert = "INSERT INTO t_djpd (yyrq,user_id,gk,gksj,qd,zd,jg,jl,xtsj)VALUES (?,?,?,?,?,?,?,?,?)";
		Connection con = DBUtil.getInstance().openConnection();
		try {
			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			con.setAutoCommit(false);
			PreparedStatement ps = con.prepareStatement(sql_insert);
			ps.setString(1, FormatUtil.getTimeFormat(sertime));
			ps.setInt(2, userid);
			ps.setString(3, username);
			ps.setString(4, usertel);
			ps.setString(5, startPos);
			ps.setString(6, desPos);
			ps.setString(7, exCost);
			ps.setString(8, exmil);
			ps.setString(9, FormatUtil.getTimeFormatNow());
			ps.executeUpdate();
			con.commit();
			return "0";
		} catch (SQLException e) {
			e.printStackTrace();
			return "1";
		} finally {
			DBUtil.getInstance().closeConnection(con);
		}
	}

	public int createFormByCall(int userid, int driid) {
		String sql_update = "UPDATE t_user SET t_user.state = 0 WHERE t_user.id = "
				+ driid;
		String sql_addForm = "INSERT INTO t_djpd (t_djpd.user_id,t_djpd.dri_id,xtsj,t_djpd.state,t_djpd.userid)VALUES (?,?,?,?,3)";
		String sql_query = "SELECT t_user.state FROM t_user WHERE t_user.id = "
				+ driid;
		Connection con = DBUtil.getInstance().openConnection();
		try {
			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			con.setAutoCommit(false);
			Statement s = con.createStatement();
			ResultSet rs1 = s.executeQuery(sql_query);
			if (rs1.next()) {
				if ((rs1.getInt(1)) == 0) {
					DBUtil.getInstance().closeConnection(con);
					return 2;// 司机正忙
				}
				PreparedStatement ps = con.prepareStatement(sql_addForm);
				ps.setInt(1, userid);
				ps.setInt(2, driid);
				ps.setString(3, FormatUtil.getTimeFormatNow());
				ps.setInt(4, 2);
				if (ps.executeUpdate() != 0) {
					Statement s1 = con.createStatement();
					if (s1.executeUpdate(sql_update) != 0) {
						con.commit();
						DBUtil.getInstance().closeConnection(con);
						return 0;// 成功
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.getInstance().closeConnection(con);
		return 1;// 失败
	}

	public List<Form> queryForms(int userid) {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * 三公里判断
	 * @param driid
	 * @return
	 */
	public static List<Form> queryAllForms1(int driid){
		List<Form> list = new ArrayList<Form>();
		String sql_query = "select id,gk,yyrq,qd,zd,jg,jl,bz,class,xtsj,DATE_ADD(xtsj,INTERVAL 3 MINUTE) date " +
				"from t_djpd  " +
				"where state = 1 and yyrq>now()";
		Connection conn = DBUtil.getInstance().openConnection();
		try{
			conn.setAutoCommit(false);
			PreparedStatement pst_query = conn.prepareStatement(sql_query);
			ResultSet rs_query = pst_query.executeQuery();
			while(rs_query.next()){
				Form f = new Form();
				
				System.out.println((rs_query.getString("date")));
				System.out.println(FormatUtil.getDateTimeNow());
				
				if(FormatUtil.dateString2long((rs_query.getString("date")))<System.currentTimeMillis()||calcByDriid(driid,conn,rs_query.getString("qd"))){
				//||calcOrder2Dri(rs_query.getString("qd")).size()!=0){//三公里的司机
					
					f = new Form(rs_query.getInt("id"),
							rs_query.getString("yyrq"), 
							rs_query.getString("gk"), 
							rs_query.getString("qd"), 
							rs_query.getString("zd"), 
							rs_query.getString("jl"), 
							rs_query.getString("jg"), 
							(rs_query.getString("bz")==null?"":rs_query.getString("bz")));
					list.add(f);
				}
			}
			
			rs_query.close();
			pst_query.close();
			conn.commit();
			conn.close();
			
			
		}catch(SQLException e){
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	//-----------------------------------------------
	
	public List<Form> queryAllForms(int driid) {
		
		List<Form> forms = new ArrayList<Form>();
		String sql_query = "SELECT t_djpd.id,t_djpd.gk,t_djpd.yyrq,t_djpd.qd,t_djpd.zd,t_djpd.jg,t_djpd.jl,t_djpd.bz,t_djpd.class,t_djpd.xtsj FROM t_djpd WHERE t_djpd.state = 1 AND t_djpd.yyrq > NOW()";
		String sql_checkingid = "SELECT id FROM t_user WHERE id=" + driid;// 可以简化
		Connection con = DBUtil.getInstance().openConnection();
		System.out.println(driid);
		try {
			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			con.setAutoCommit(false);
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(sql_checkingid);
			if (rs.next()) {
				PreparedStatement ps = con.prepareStatement(sql_query);
				rs = null;
				rs = ps.executeQuery();
				while (rs.next()) {
					Integer id = rs.getInt("id");
					if (id != null && rs.getString("yyrq") != null
							&& rs.getString("gk") != null
							&& rs.getString("qd") != null
							&& rs.getString("zd") != null
							&& rs.getString("jl") != null
							&& rs.getString("jg") != null){
//							&& rs.getString("class") !=null){
						Form f = null;
						f = new Form(id,rs.getString("yyrq"), rs
								.getString("gk"), rs.getString("qd"), rs
								.getString("zd"), rs.getString("jl"), rs
								.getString("jg"), (rs.getString("bz")==null?"":rs.getString("bz")));
						forms.add(f);
					}
				}
				con.commit();
				DBUtil.getInstance().closeConnection(con);
				return forms;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBUtil.getInstance().closeConnection(con);
		return null;
	}
//-------------------------------
	public int selectForm(int formid, int driid) {
		
		
		String sql_query = "select " + 
				"d.id," +//1
				"u.state u_state," +//2
				"d.state d_state," +//3
				"d.dri_id," +//4
				"h.sjh," +//5
				"u.xm," +//6
				"u.dh," +//7
				"u.stop_time," +//8
				"stop_num " +//9
				"from t_djpd d,t_user u,t_hy h " +
				"where yyrq>NOW() and  h.id = d.user_id AND d.id = "+formid+" AND u.id = "+driid;
		
		String sql_change_state = "UPDATE t_user,t_djpd SET t_user.state = 0,t_djpd.state = 2,t_djpd.qrbz = 1,t_djpd.djsj = t_user.username,t_djpd.dri_id = t_user.id,order_change_time=now() WHERE t_user.id = "
				+ driid + " AND t_djpd.id = " + formid;
		Connection con = DBUtil.getInstance().openConnection();
		try {
			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			con.setAutoCommit(false);
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(sql_query);
//如果该订单有司机,那么订单不能被抢
			if (rs.next()){
				
				if (rs.getInt("u_state") == 1 && rs.getInt("d_state") == 1) {
					
					System.out.println("user state:"+rs.getInt("u_state")+"order state:"+rs.getInt("d_state"));
					
					String driName = rs.getString(6).trim();
					String stopTime = (rs.getString("stop_time"))==null?"1970-00-00 00:00:00":(rs.getString("stop_time")).trim();
					String stopNum = (rs.getString("stop_num"))== null?"0":(rs.getString("stop_num")).trim();
					
					
					String userTel = rs.getString("sjh").trim();//==null?"15900617334":rs.getString("sjh").trim();
					String driTel = rs.getString("dh").trim();
					
					if(stopTime.equals("1970-00-00 00:00:00") && stopNum.equals("0")){
						
						System.out.println("stopTime:"+stopTime+"stopNum:"+stopNum);
						System.out.println("账号正常,可以抢单..");
						
					}else{
						System.out.println("账号"+FormatUtil.getTimeFormat(FormatUtil.dateString2long(stopTime)+(3*24*3600000))+"解封");
					}
					
					if((Integer.parseInt(stopNum)<4 || Integer.parseInt(stopNum)==0) 
							&& ((System.currentTimeMillis()>(FormatUtil.dateString2long(stopTime)+(3*24*3600000))))
							&& DriRandomBonusImpl.makeBonusDao(formid,con)
							){
						s.executeUpdate(sql_change_state);
						con.commit();
						
						System.out.println("用户电话:"+userTel);
						SendSMSUtil.getInstance().sendSMS(userTel,URLEncoder.encode("您好,你的代驾信息已被我司"+driName+"师傅接收,稍后会跟您联络!", "gbk"));
						
						con.commit();
						return 0;
					}
					con.rollback();
					con.close();
					return 5;//司机账号被锁定
				}
				con.rollback();
				con.close();
				return 2;// 司机或者订单原始状态错误
			}
			con.rollback();
			con.close();
			return 1;// 订单或者司机不存在
  
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
				con.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			
			return 3;// spl错误
		} catch(UnsupportedEncodingException e){
			e.printStackTrace();
			try {
				con.rollback();
				con.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return 4;//
		}
		finally {
			DBUtil.getInstance().closeConnection(con);
		}
	}

	public Form querySelectedForms(int driid) {
		
		System.out.println("查询订单信息.");
		
		String sql_query = "select d.id,d.yyrq,d.gk,d.gksj,d.qd,d.zd,d.jl,d.jg,d.state,d.card_id,d.Discount,d.bz,d.bz,d.bonus_cost,d.class, " +
				" u.xm,u.dh,u.username  " +
				" from t_djpd d , t_user u where d.state <6 AND d.state>1 " +
				" AND u.id = d.dri_id AND d.dri_id = "+driid;
		
		String sql_query_card = "select card_cost,card_mil,times,card_state from t_card where id =?";
		
		try{
			Connection conn = DBUtil.getInstance().openConnection();
			conn.setAutoCommit(false);
			PreparedStatement pst = conn.prepareStatement(sql_query);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Form form  = new Form();
				form.setFormid(rs.getInt("id"));//1
				form.setSertime(rs.getString("yyrq"));//2
				form.setDriname(rs.getString("xm"));//3
				form.setDritel(rs.getString("dh"));//4
				form.setTel(rs.getString("gksj"));//5
				form.setName(rs.getString("gk"));//6
				form.setDrinum(rs.getString("username"));//7
				form.setStartpos(rs.getString("qd"));//8
				form.setDespos(rs.getString("zd"));//9
				form.setExmil(rs.getString("jl"));//10
				form.setExcost(rs.getString("jg"));//11
				form.setFormstate(rs.getInt("state"));//12
				form.setOrderclass(rs.getInt("class"));
				form.setBz(rs.getString("bz")==null?"":rs.getString("bz"));
				form.setBonus(rs.getString("bonus_cost"));
//				form.setCardstate(rs.getString("card_state"));
				String card_id = rs.getString("card_id");
				System.out.println("card_id"+card_id);
				if(card_id==null){
					form.setCardvalue("null");
				}else {
					PreparedStatement pst_query_card = conn.prepareStatement(sql_query_card);
					pst_query_card.setInt(1, Integer.parseInt(card_id));
					ResultSet rs_card = pst_query_card.executeQuery();
					while(rs_card.next()){
						int card_state = rs_card.getInt("card_state");
						form.setCardstate(card_state+"");//13
						if(card_state==1){//为金额 
							form.setCardvalue(rs.getString("card_cost"));
						}else if(card_state==2){//2为里程
							form.setCardvalue(rs.getString("card_mil"));
						}else if(card_state==3){//3为次数
							form.setCardvalue(rs.getString("times"));
						}else {
							form.setCardvalue("null");
						}
					}
					rs_card.close();
					pst_query_card.close();
				}
				
				System.out.println(form.toString());
				rs.close();
				conn.commit();
				conn.close();
				return form;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		
//		Connection con = DBUtil.getInstance().openConnection();
//		try {
//			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
//			PreparedStatement ps = con.prepareStatement(sql_query);
//			ResultSet rs = ps.executeQuery();
//			while(rs.next()) {
//				
//				int i = rs.getInt("card_state");
//				Form form  = new Form();
//			
//				form.setFormid(rs.getInt("id"));
//				form.setSertime(rs.getString("yyrq"));
//				form.setDriname(rs.getString("xm"));
//				form.setDritel(rs.getString("dh"));
//				form.setTel(rs.getString("gjsj"));
//				form.setName(rs.getString("gk"));
//				form.setDrinum(rs.getString("username"));
//				form.setStartpos(rs.getString("qd"));
//				form.setDespos(rs.getString("zd"));
//				form.setExmil(rs.getString("jl"));
//				form.setExcost(rs.getString("jg"));
//				form.setCardstate(rs.getString("card_state"));
//				
//				if(i==1){//为金额 
//					form.setCardvalue(rs.getString("card_cost"));
//				}else if(i==2){//2为里程
//					form.setCardvalue(rs.getString("card_mil"));
//				}else if(i==3){//3为次数
//					form.setCardvalue(rs.getString("times"));
//				}else {
//					form.setCardvalue("null");
//				}
//				form.setFormstate(rs.getInt("state"));
//				
//				return form;
//			}
//			rs.close();
//			DBUtil.getInstance().closeConnection(con);
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		DBUtil.getInstance().closeConnection(con);
		return null;
	}

	public int addForm(int formid, String startpos, String despos,
			String exmil, String sertime, String excost) {
		// TODO Auto-generated method stub
		return 0;
	}
	
	/**
	 * 计算司机与预约订单之间的距离
	 * @param address
	 * @param lng
	 * @param lat
	 * @return
	 */
//	private int calcDistance(String address,String lng_dri,String lat_dri){
//		
//		address = "上海市"+address;
//		Map<String,String> map = LatitudeUtils.getGeocoderLatitude(address);
//		String lng_map = map.get("lng");
//		String lat_map = map.get("lat");
//		double mil = MilUtil.gps2m(Double.parseDouble(lat_map), 
//				Double.parseDouble(lng_map), 
//				Double.parseDouble(lat_dri), 
//				Double.parseDouble(lng_dri));
//		
//		return (int)mil/1000;
//	}

	/**
	 * 根据地址计算三公里以内的司机
	 * @param address
	 * @return
	 */
	private static Map<String,DriXYBean> calcOrder2Dri(String address){
		
		String sql = "select id,sjuserid,x,y from t_sj_xy where sjuserid>3000 or sjuserid<2000";
		String dri_id;
		DriXYBean driXY;
		
		Map<String,DriXYBean> map = new HashMap<String,DriXYBean>();
		Connection conn = DBUtil.getInstance().openConnection();
		
		
		try{
			Map<String,String> map_order = LatitudeUtils.getGeocoderLatitude("上海市"+address);
			String order_lng = map_order.get("lng");
			String order_lat = map_order.get("lat");
			
			conn.setAutoCommit(false);
			PreparedStatement pst = conn.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				driXY = new DriXYBean();
				//driXY.setDri_id(dri_id=rs.getString("sjuserid"));
				dri_id = rs.getString("sjuserid");
				driXY.setDri_lng(rs.getString("x"));
				driXY.setDri_lat(rs.getString("y"));
				
				if(MilUtil.gps2m(Double.parseDouble(driXY.getDri_lat()),
						Double.parseDouble(driXY.getDri_lng()), 
						Double.parseDouble(order_lat),
						Double.parseDouble(order_lng))/1000<3){
					map.put(dri_id, driXY);
				}else{
					map.put(dri_id, null);
				}
				
			}
			
			rs.close();
			pst.close();
			conn.commit();
			
			return map;
		}catch(SQLException e){
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				return null;
			}
		}
		
		DBUtil.getInstance().closeConnection(conn);
		return null;
	}
	
	/**
	 * 根据司机id计算是否在三公里之内
	 * @param address
	 * @return
	 */
	private static boolean calcByDriid(int driid,Connection conn,String address){
		boolean b = false;
		String sql_query = "select id,x,y from t_sj_xy where sjuserid=?";
		DriXYBean driXY;
		Map<String,String> map_order = LatitudeUtils.getGeocoderLatitude("上海市"+address);
		String order_lng = map_order.get("lng");
		String order_lat = map_order.get("lat");
		
		
		try{
			PreparedStatement pst_query = conn.prepareStatement(sql_query);
			ResultSet rs_query = pst_query.executeQuery();
			
			while(rs_query.next()){
				driXY = new DriXYBean();
				driXY.setDri_lng(rs_query.getString("x"));
				driXY.setDri_lat(rs_query.getString("y"));
				
				if(MilUtil.gps2m(Double.parseDouble(driXY.getDri_lat()),Double.parseDouble(driXY.getDri_lng()), Double.parseDouble(order_lat),Double.parseDouble(order_lng))/1000<3){
					return true;
				}
			}
			rs_query.close();
			pst_query.close();
			
		}catch(SQLException e){
			e.printStackTrace();
		}
		return false;
	}

	
	public static void main(String[] args) {
		System.out.println(queryAllForms1(340).toString());
	}
}
